﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DreamIn.Structures.ServerDatabase.Entities;
using MySql.Data.MySqlClient;
using DreamIn.Structures.LocalDB;

namespace DreamIn.Structures.ServerDatabase.DataAccessLayer
{
    class UserDAL
    {
        public UserEntity GetUser(String username)
        {
            MySqlConnection connection = DALHelper.Connection;
            try
            {
               MySqlCommand command = connection.CreateCommand();
               MySqlDataReader Reader;

               command.CommandText = "select id_user,username,password,country from users where username="+username;
               connection.Open();
               Reader = command.ExecuteReader();
               List<UserEntity> result=new List<UserEntity>();
               while (Reader.Read()) {
                   return new UserEntity() { id_user = Reader.GetInt16(0), username=Reader.GetString(1), password=Reader.GetString(2), country=Reader.GetString(3)};
                   
               }
               Reader.Close();
               return null;
            }
            finally
            {
                connection.Close();
            }
        }

        public List<UserEntity> GetUsers()
        {
            MySqlConnection connection = DALHelper.Connection;
            try
            {
                MySqlCommand command = connection.CreateCommand();
                MySqlDataReader Reader;

                command.CommandText = "select id_user,username,password,country from users";
                connection.Open();
                Reader = command.ExecuteReader();
                List<UserEntity> result = new List<UserEntity>();
                while (Reader.Read())
                {
                    result.Add(new UserEntity() { id_user = Reader.GetInt16(0), username = Reader.GetString(1), password = Reader.GetString(2), country = Reader.GetString(3) });
                }
                Reader.Close();
                return result;
            }
            finally
            {
                connection.Close();
            }
        }

        public void AddUser(String username, String password, String country)
        {
            MySqlConnection connection = DALHelper.Connection;
            MD5Cryptography md = new MD5Cryptography();
            try
            {
                MySqlCommand command = connection.CreateCommand();

                connection.Open();
                command.CommandText = "INSERT INTO users (username, password, country) VALUES" + "('" + username + "','" + md.getMd5Password(password) + "','" + country + "')";
                command.ExecuteNonQuery();
            }
            finally
            {
                connection.Close();
            }
        }

        public void DeleteUser(UserEntity user)
        {
            MySqlConnection connection = DALHelper.Connection;
            try
            {
                MySqlCommand command = connection.CreateCommand();

                connection.Open();
                command.CommandText = "DELETE FROM users WHERE id_user=" + user.id_user;
                command.ExecuteNonQuery();
            }
            finally
            {
                connection.Close();
            }
        }

        public void UpdateUser(int id_user,String username,String password,String country)
        {
            MySqlConnection connection = DALHelper.Connection;
            try
            {
                MySqlCommand command = connection.CreateCommand();
                MD5Cryptography md = new MD5Cryptography();

                connection.Open();
                command.CommandText = "UPDATE users SET username='" + username + "', password='" + md.getMd5Password(password) + "', country='" + country + "' WHERE id_user=" + id_user;
                command.ExecuteNonQuery();
            }
            finally
            {
                connection.Close();
            }
        }
    }
}
